package com.hzit.dao;

import com.hzit.bean.Dept;
import com.hzit.bean.Emp;
import com.hzit.dao.base.BaseDao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Emp操作数据库相关的类
 */
public class EmpDao extends BaseDao {


    /**
     * 获取所有员工列表
     *
     * @return
     */
    public List<Emp> findAll(int page, int pageSize) {

        //1.获取连接
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;

        List<Emp> empList = new ArrayList<>();


        try {
            //2.编写 sql ,执行
            // String sql = "SELECT * FROM emp LIMIT ?,?";
            String sql = "SELECT * FROM emp INNER JOIN dept ON emp.`deptno` = dept.`deptno` LIMIT ?,?";
            preparedStatement = connection.prepareStatement(sql);

            //1--> 0  2
            //2--> 2  2
            //3--> 4  2

            //起始参数  (page-1)*pageSize    pageSize
            preparedStatement.setInt(1, (page - 1) * pageSize);
            preparedStatement.setInt(2, pageSize);


            //3.获得结果集
            rs = preparedStatement.executeQuery();

            //4.解析并返回结果

            Emp emp = null;
            while (rs.next()) {

                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                String job = rs.getString("job");
                int mgr = rs.getInt("mgr");
                Date hireDate = rs.getDate("hireDate");
                double sal = rs.getDouble("sal");
                int deptno = rs.getInt("deptno");

                //部门信息
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");

                Dept dept = new Dept(deptno, dname, loc);


                emp = new Emp(empno, ename, job, mgr, hireDate, sal, deptno);
                emp.setDept(dept);

                //添加到集合
                empList.add(emp);

            }

            return empList;

        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("----->封装emp报错");
        } finally {
            this.closeAll(rs, preparedStatement, connection);
        }
        return null;
    }


    /**
     * 根据员工编号删除信息
     *
     * @param id
     * @return
     */
    public int deleteByPrimary(int id) {
        String sql = "DELETE FROM emp WHERE empno = ?";
        int row = this.baseExecuteUpdate(sql, id);
        return row;
    }

    public int insertEmp(Emp emp) {
        String sql = "INSERT INTO emp(ename,job,mgr,hireDate,sal,deptno) VALUES(?,?,?,?,?,?)";
        int row = this.baseExecuteUpdate(sql, emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHireDate(), emp.getSal(), emp.getDeptno());
        return row;
    }

    /**
     * 根据主键查找 对象
     *
     * @param empno
     * @return
     */
    public Emp findEmpByPrimary(Integer empno) {
        //1.获取连接
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;


        try {
            //2.编写 sql ,执行
            String sql = "SELECT * FROM emp WHERE empno=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, empno);
            //3.获得结果集
            rs = preparedStatement.executeQuery();

            //4.解析并返回结果

            Emp emp = null;
            while (rs.next()) {

                //int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                String job = rs.getString("job");
                int mgr = rs.getInt("mgr");
                Date hireDate = rs.getDate("hireDate");
                double sal = rs.getDouble("sal");
                int deptno = rs.getInt("deptno");

                emp = new Emp(empno, ename, job, mgr, hireDate, sal, deptno);


            }

            return emp;

        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("----->封装emp报错");
        } finally {
            this.closeAll(rs, preparedStatement, connection);
        }
        return null;
    }

    public int updateEmp(Emp emp) {
        String sql = "UPDATE emp SET ename=?,job=?,mgr=?,hireDate=?,sal=?,deptno=? WHERE empno=?";
        int row = this.baseExecuteUpdate(sql, emp.getEname(), emp.getJob(), emp.getMgr(), emp.getHireDate(), emp.getSal(), emp.getDeptno(), emp.getEmpno());
        return row;
    }

    /**
     * 搜索
     *
     * @param searchType
     * @param searchVal
     * @param page
     * @param pageSize
     * @return
     */
    public List<Emp> findSearch(String searchType, String searchVal, int page, int pageSize) {
        //1.获取连接
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;

        List<Emp> empList = new ArrayList<>();


        int index = 0;
        try {
            //2.编写 sql ,执行
            //String sql = "SELECT * FROM emp";
            String sql = "SELECT * FROM emp INNER JOIN dept ON emp.`deptno` = dept.`deptno` ";
            if (searchType != null && !searchType.equals("-1")) {  // 选择了查询类型
                // SELECT * FROM emp WHERE empno = xxx
                // if(searchType.equals("deptno")){
                sql += " WHERE emp." + searchType + " = ?";
                //}else {
                //     sql += " WHERE " + searchType + " = ?";
                // }

            } else { //没有选择查询类型

                if (searchVal != null) {  //如果给力查询值，默认查询ename 字段
                    sql += " WHERE ename = ?";
                }

            }

            //分页sql
            sql += " LIMIT ?,? ";

            preparedStatement = connection.prepareStatement(sql);
            if ((searchType != null && !searchType.equals("-1")) || searchVal != null) {
                preparedStatement.setObject(1, searchVal);
                index = 1;
            }

            preparedStatement.setInt(++index, (page - 1) * pageSize);
            preparedStatement.setInt(++index, pageSize);

//            if (searchVal != null) {
//                preparedStatement.setObject(1, searchVal);
//            }

            System.out.println("拼接搜索sql----->" + sql);
            //3.获得结果集
            rs = preparedStatement.executeQuery();

            //4.解析并返回结果

            Emp emp = null;
            while (rs.next()) {

                int empno = rs.getInt("empno");
                String ename = rs.getString("ename");
                String job = rs.getString("job");
                int mgr = rs.getInt("mgr");
                Date hireDate = rs.getDate("hireDate");
                double sal = rs.getDouble("sal");
                int deptno = rs.getInt("deptno");

                //部门信息
                String dname = rs.getString("dname");
                String loc = rs.getString("loc");

                Dept dept = new Dept(deptno, dname, loc);

                emp = new Emp(empno, ename, job, mgr, hireDate, sal, deptno);
                emp.setDept(dept);

                //添加到集合
                empList.add(emp);
            }
            return empList;
        } catch (SQLException e) {
            e.printStackTrace();
            System.err.println("----->封装emp报错");
        } finally {
            this.closeAll(rs, preparedStatement, connection);
        }
        return null;
    }

    public long findCount() {
        //1.获取连接
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;

        try {
            //2.编写 sql ,执行
            String sql = "SELECT COUNT(1) cnt FROM emp";
            preparedStatement = connection.prepareStatement(sql);
            //3.获得结果集
            rs = preparedStatement.executeQuery();

            //4.解析并返回结果
            if (rs.next()) {
                long cnt = rs.getLong("cnt");
                return cnt;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(rs, preparedStatement, connection);
        }
        return 0;
    }

    public int deleteByIds(String[] ids) {
        //DELETE FROM emp WHERE empno in(?,?)
        String sql = "DELETE FROM emp WHERE empno in(";
        if (ids != null && ids.length > 0) {
            for (int i = 0; i < ids.length; i++) {
                if (i != ids.length - 1) {
                    sql += " " + ids[i] + ",";
                } else {
                    sql += " " + ids[i];
                }
            }
        } else {
            sql += "-1";
        }
        sql += ")";

        int row = this.baseExecuteUpdate(sql);
        return row;
    }

    public boolean isEnameExist(String ename) {
        //1.获取连接
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;

        try {
            //2.编写 sql ,执行
            String sql = "SELECT COUNT(1) cnt FROM emp WHERE ename=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, ename);

            //3.获得结果集
            rs = preparedStatement.executeQuery();


            //4.解析并返回结果
            if (rs.next()) {
                long cnt = rs.getLong("cnt");
                return cnt > 0;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeAll(rs, preparedStatement, connection);
        }
        return false;
    }
}
